﻿using System;
using System.Text;
using System.Linq;
using System.Reflection;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Options;
using Fast.Framework.Interfaces;
using Fast.Framework.Extensions;
using Fast.Framework.Models;


namespace Fast.Framework
{

    /// <summary>
    /// Ado实现类
    /// </summary>
    public class Ado<TDbOptions> : IAdo<TDbOptions> where TDbOptions : DbOptions
    {
        /// <summary>
        /// 释放标识
        /// </summary>
        private bool disposed = false;

        /// <summary>
        /// 数据库提供商工厂
        /// </summary>
        public DbProviderFactory DbProviderFactory { get; }

        /// <summary>
        /// 参数符号
        /// </summary>
        public string ParameterNotation { get; }

        /// <summary>
        /// 标识符
        /// </summary>
        public string Identifier { get; }

        /// <summary>
        /// 数据库选项
        /// </summary>
        public TDbOptions DbOptions { get; }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="dbOptions">数据库选项</param>
        public Ado(IOptionsSnapshot<TDbOptions> dbOptions)
        {
            DbOptions = dbOptions.Value;
            DbProviderFactories.RegisterFactory(DbOptions.ProviderName, DbOptions.FactoryName);
            DbProviderFactory = DbProviderFactories.GetFactory(DbOptions.ProviderName);
            ParameterNotation = GetParameterNotation();
            Identifier = GetIdentifier();
        }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="dbOptions">数据选项</param>
        public Ado(TDbOptions dbOptions)
        {
            DbOptions = dbOptions;
            DbProviderFactories.RegisterFactory(DbOptions.ProviderName, DbOptions.FactoryName);
            DbProviderFactory = DbProviderFactories.GetFactory(DbOptions.ProviderName);
            ParameterNotation = GetParameterNotation();
            Identifier = GetIdentifier();
        }

        #region 私有方法
        /// <summary>
        /// 获取数据库参数符号
        /// </summary>
        /// <returns></returns>
        private string GetParameterNotation()
        {
            if (DbOptions.DbType == Models.DbType.Oracle)
            {
                return ":";
            }
            return "@";
        }

        /// <summary>
        /// 获取标识符
        /// </summary>
        /// <returns></returns>
        private string GetIdentifier()
        {
            if (DbOptions.DbType == Models.DbType.SQLServer || DbOptions.DbType == Models.DbType.SQLite)
            {
                return "[]";
            }
            else if (DbOptions.DbType == Models.DbType.MySQL)
            {
                return "``";
            }
            else if (DbOptions.DbType == Models.DbType.Oracle || DbOptions.DbType == Models.DbType.PostgreSQL)
            {
                return "\"\"";
            }
            throw new NotSupportedException($"{DbOptions.DbType}不支持获取标识符的数据库类型");
        }
        #endregion

        /// <summary>
        /// 执行非查询异步
        /// </summary>
        /// <param name="sqlOrProName">Sql语句或存储过程</param>
        /// <param name="dbParameters">数据库参数</param>
        /// <param name="isProcedure">是否存储过程</param>
        /// <param name="isTransaction">是否事务</param>
        /// <returns></returns>
        public async Task<int> ExecuteNonQueryAsync(string sqlOrProName, List<DbParameter> dbParameters = null, bool isProcedure = false, bool isTransaction = false)
        {
            DbConnection conn = DbProviderFactory.CreateConnection();
            DbCommand cmd = conn.CreateCommand();
            conn.ConnectionString = DbOptions.ConnectionStrings;
            try
            {
                await conn.OpenAsync();
                cmd.CommandText = sqlOrProName;
                int result;
                if (dbParameters != null)
                {
                    if (DbOptions.DbType == Models.DbType.Oracle)
                    {
                        cmd.GetType().GetProperty("BindByName").SetValue(cmd, true);
                    }
                    cmd.Parameters.AddRange(dbParameters.ToArray());
                }
                if (isProcedure)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                }
                if (isTransaction)
                {
                    cmd.Transaction = await conn.BeginTransactionAsync();
                    result = await cmd.ExecuteNonQueryAsync();
                    await cmd.Transaction.CommitAsync();
                }
                else
                {
                    result = await cmd.ExecuteNonQueryAsync();
                }
                return result;
            }
            catch
            {
                if (isTransaction && cmd.Transaction != null)
                {
                    await cmd.Transaction.RollbackAsync();
                }
                throw;
            }
            finally
            {
                cmd.Parameters.Clear();
                await conn.CloseAsync();
            }
        }

        /// <summary>
        /// 执行标量异步
        /// </summary>
        /// <typeparam name="T">需要转换的类型</typeparam>
        /// <param name="sqlOrProName">Sql语句或存储过程</param>
        /// <param name="dbParameters">数据库参数</param>
        /// <param name="isProcedure">是否存储过程</param>
        public async Task<T> ExecuteScalarAsync<T>(string sqlOrProName, List<DbParameter> dbParameters = null, bool isProcedure = false)
        {
            DbConnection conn = DbProviderFactory.CreateConnection();
            DbCommand cmd = conn.CreateCommand();
            conn.ConnectionString = DbOptions.ConnectionStrings;
            try
            {
                await conn.OpenAsync();
                cmd.CommandText = sqlOrProName;
                if (dbParameters != null)
                {
                    if (DbOptions.DbType == Models.DbType.Oracle)
                    {
                        cmd.GetType().GetProperty("BindByName").SetValue(cmd, true);
                    }
                    cmd.Parameters.AddRange(dbParameters.ToArray());
                }
                if (isProcedure)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                }
                var obj = await cmd.ExecuteScalarAsync();
                return obj.ChanageType<T>();
            }
            finally
            {
                cmd.Parameters.Clear();
                await conn.CloseAsync();
            }
        }

        /// <summary>
        /// 执行读取器异步
        /// </summary>
        /// <param name="sqlOrProName">sql语句或者存储过程</param>
        /// <param name="dbParameters">数据库参数</param>
        /// <param name="isProcedure">是否存储过程</param>
        /// <returns></returns>
        public async Task<DbDataReader> ExecuteReaderAsync(string sqlOrProName, List<DbParameter> dbParameters = null, bool isProcedure = false)
        {
            DbConnection conn = DbProviderFactory.CreateConnection();
            DbCommand cmd = conn.CreateCommand();
            conn.ConnectionString = DbOptions.ConnectionStrings;
            await conn.OpenAsync();
            cmd.CommandText = sqlOrProName;
            if (dbParameters != null)
            {
                if (DbOptions.DbType == Models.DbType.Oracle)
                {
                    cmd.GetType().GetProperty("BindByName").SetValue(cmd, true);
                }
                cmd.Parameters.AddRange(dbParameters.ToArray());
            }
            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return reader;
        }

        /// <summary>
        /// Sql参数构建
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        public List<DbParameter> SqlParametersBuild<T>(T entity) where T : class
        {
            if (entity == null)
            {
                throw new ArgumentNullException(nameof(entity));
            }
            List<DbParameter> dbParameters = new List<DbParameter>();

            Type type = typeof(T);
            var propertyInfos = type.GetProperties().Where(w => !w.PropertyType.IsGenericType || w.PropertyType.IsGenericType && w.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));

            foreach (var propertyInfo in propertyInfos)
            {
                var dbParameter = DbProviderFactory.CreateParameter();
                dbParameter.ParameterName = $"{ParameterNotation}{propertyInfo.Name}";
                dbParameter.Value = propertyInfo.GetValue(entity) ?? DBNull.Value;
                dbParameters.Add(dbParameter);
            }
            return dbParameters;
        }

        /// <summary>
        /// Sql参数构建
        /// </summary>
        /// <param name="keyValues"></param>
        /// <returns></returns>
        public List<DbParameter> SqlParametersBuild(Dictionary<string, object> keyValues)
        {
            var dbParameters = new List<DbParameter>();
            foreach (var key in keyValues.Keys)
            {
                var dbParameter = DbProviderFactory.CreateParameter();
                dbParameter.ParameterName = $"{ParameterNotation}{key}";
                dbParameter.Value = keyValues[key] ?? DBNull.Value;
                dbParameters.Add(dbParameter);
            }
            return dbParameters;
        }

        #region 释放
        /// <summary>
        /// 释放方法
        /// </summary>
        /// <param name="disposing">释放标识</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    //托管代码释放
                }
                //非托管代码释放
                disposed = true;
            }
        }

        /// <summary>
        /// 释放方法
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 析构方法
        /// </summary>
        ~Ado()
        {
            Dispose(false);
        }
        #endregion
    }
}
